﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DataAssistant;
using System.Data.OracleClient;
using System.Data;


/// <summary>
/// Summary description for DataLayer
/// </summary>
public class DataLayer
{

    #region Reports
	/// <summary>
	/// Detailed Attendance Report
	/// </summary>
	/// <param name="connectionString"></param>
	/// <param name="from"></param>
	/// <param name="end"></param>
	/// <param name="codes"></param>
	/// <param name="message"></param>
	/// <returns></returns>
	public DataSet DeatiledAttendanceReport(
		string connectionString,
		DateTime from,
		DateTime to,
		String codes,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_result", OracleType.Cursor),
					new OracleParameter("p_from_date", OracleType.DateTime),
					new OracleParameter("p_to_date", OracleType.DateTime),
					new OracleParameter("p_codes", OracleType.VarChar, codes.Length + 1)
				};

			spParams[0].Direction = ParameterDirection.Output;
			spParams[1].Value = from;
			spParams[2].Value = to;
			spParams[3].Value = codes.Trim();


			DataSet result = OraDataAssistant.GetSPDataSet(connectionString, "pkg_reports.detailed_attendance", spParams, ref message);


			return result;

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	public DataSet CourseAttendanceReport(string connectionString,
		decimal semesterId,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_result", OracleType.Cursor),
					new OracleParameter("p_semester_id", OracleType.Number)
				};

			spParams[0].Direction = ParameterDirection.Output;
			spParams[1].Value =semesterId;


			DataSet result = OraDataAssistant.GetSPDataSet(connectionString, "pkg_reports.course_attendance", spParams, ref message);


			return result;

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	
	public DataSet TeachersActivityReport(string connectionString,
		decimal sectionId,
		decimal teacherId,
		decimal semesterId,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_result", OracleType.Cursor),
					new OracleParameter("p_current_week", OracleType.VarChar, 20),
					new OracleParameter("p_prior_week", OracleType.VarChar, 20)
				};

			spParams[0].Direction = ParameterDirection.Output;
			spParams[1].Direction = ParameterDirection.Output;
			spParams[2].Direction = ParameterDirection.Output;


			DataSet result = OraDataAssistant.GetSPDataSet(connectionString, "pkg_reports.teacher_activity", spParams, ref message);

		
			return result;

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}
	
	public DataSet AllStudentsAttendanceReport(string connectionString,
		decimal sectionId,
		decimal teacherId,
		decimal semesterId,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_result", OracleType.Cursor),
					new OracleParameter("p_current_week", OracleType.VarChar, 20),
					new OracleParameter("p_prior_week", OracleType.VarChar, 20)
				};

			spParams[0].Direction = ParameterDirection.Output;
			spParams[1].Direction = ParameterDirection.Output;
			spParams[2].Direction = ParameterDirection.Output;


			DataSet result = OraDataAssistant.GetSPDataSet(connectionString, "pkg_reports.all_students_attendance", spParams, ref message);

			result.Tables[0].Columns[7].ColumnName = string.Format("Current Week {0}", spParams[1].Value);

			if (spParams[2].Value != null)
			{
				result.Tables[0].Columns[6].ColumnName = string.Format("Prior Week {0}", spParams[2].Value);
			}
			else
			{
				result.Tables[0].Columns.RemoveAt(6);
			}
			return result;

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	public DataSet GetDemographicReport(string connectionString, 
		decimal sectionId, 
		decimal teacherId, 
		decimal semesterId,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_result", OracleType.Cursor),
					new OracleParameter("p_section_id", OracleType.Number),
					new OracleParameter("p_teacher_id", OracleType.Number),
					new OracleParameter("p_semester_id", OracleType.Number)
				};

			spParams[0].Direction = ParameterDirection.Output;
			spParams[1].Value =  sectionId;
			spParams[2].Value = teacherId;
			spParams[3].Value = semesterId;
			

			return OraDataAssistant.GetSPDataSet(connectionString, "pkg_reports.demographics", spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}


	public DataTable GetEmailDeliveryReport(string connectionString,
		DateTime dateFrom,
		DateTime dateTo,
		decimal? sectionId,
		out string message)
	{
		try
		{
			message = "";

			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_result", OracleType.Cursor),
					new OracleParameter("p_from", OracleType.DateTime),
					new OracleParameter("p_to", OracleType.DateTime),
					new OracleParameter("p_section_id", OracleType.Number),
				};

			spParams[0].Direction = ParameterDirection.Output;
			spParams[1].Value = dateFrom;
			spParams[2].Value = dateTo;
			if (sectionId == null)
				spParams[3].Value = DBNull.Value;
			else
				spParams[3].Value = sectionId;

			DataTable dt = OraDataAssistant.GetSPDataTable(connectionString, "PKG_BB.EMAIL_DELIVERY_REPORT", spParams, ref message);

			return dt;
		}
		catch (Exception ex)
		{
			message = ex.Message;
		}

		return null;
	}

	public DataTable GetEmailDeliveryDetails(string connectionString,
		DateTime date,
		decimal? status,
		out string message)
	{
		try
		{
			message = "";

			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_result", OracleType.Cursor),
					new OracleParameter("p_date", OracleType.DateTime),
					new OracleParameter("p_status", OracleType.Number)
				};

			spParams[0].Direction = ParameterDirection.Output;
			spParams[1].Value = date;
			if (status == null)
				spParams[2].Value = DBNull.Value;
			else
				spParams[2].Value = status;

			DataTable dt = OraDataAssistant.GetSPDataTable(connectionString, "PKG_BB.EMAIL_DELIVERY_DETAILS", spParams, ref message);

			return dt;
		}
		catch (Exception ex)
		{
			message = ex.Message;
		}

		return null;
	}

	public DataTable GetStudentEmailHistory(string connectionString,
		decimal studentId,
		out string message)
	{
		try
		{
			message = "";

			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_result", OracleType.Cursor),
					new OracleParameter("p_student_id", OracleType.Number)
				};

			spParams[0].Direction = ParameterDirection.Output;
			spParams[1].Value = studentId;


			DataTable dt = OraDataAssistant.GetSPDataTable(connectionString, "PKG_BB.STUDENT_EMAIL_HISTORY", spParams, ref message);

			return dt;
		}
		catch (Exception ex)
		{
			message = ex.Message;
		}

		return null;
	}

	public DataTable GetEmailDeliverySettings(string connectionString,
		out string message)
	{
		try
		{
			message = "";

			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Direction = ParameterDirection.Output;

			DataTable dt = OraDataAssistant.GetSPDataTable(connectionString, "PKG_BB.get_email_schedule", spParams, ref message);

			return dt;
		}
		catch (Exception ex)
		{
			message = ex.Message;
		}

		return null;
	}

	public void SaveEmailDeliverySettings(string connectionString, 
		DateTime date, 
		decimal frequency,
		out string message)
	{
		try
		{
			message = "";

			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_date", OracleType.DateTime),
					new OracleParameter("p_frequency", OracleType.Number)
				};

			spParams[0].Value = date;
			spParams[1].Value = frequency;


			OraDataAssistant.ExecuteSP(connectionString, "PKG_BB.set_email_schedule", ref spParams, ref message);

		}
		catch (Exception ex)
		{
			message = ex.Message;
		}
	}

	public static DataSet GetEmailingWeeklyProgressReport(string connectionString, decimal studentId, out string message)
	{
		try
		{
			message = "";

			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_students", OracleType.Cursor),
					new OracleParameter("p_grades", OracleType.Cursor)
					
				};

			spParams[0].Value = studentId;
			spParams[1].Direction = ParameterDirection.Output;
			spParams[2].Direction = ParameterDirection.Output;

			DataSet dt = OraDataAssistant.GetSPDataSet(connectionString, "PKG_BB.WPR_BY_STUDENT", spParams, ref message);

			return dt;
		}
		catch (Exception ex)
		{
			message = ex.Message;
		}

		return null;
	}


	public static DataTable GetWeeklyProgressReportBody(string connectionString, decimal id, out string message)
	{
		try
		{
			message = "";

			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_result", OracleType.Cursor),
					new OracleParameter("p_id", OracleType.Number)
					
				};

			spParams[0].Direction = ParameterDirection.Output;
			spParams[1].Value = id;

			DataTable dt = OraDataAssistant.GetSPDataTable(connectionString, "PKG_BB.get_wpr_body", spParams, ref message);

			return dt;
		}
		catch (Exception ex)
		{
			message = ex.Message;
		}

		return null;
	}

	public static decimal SavePdfInDB(string connectionString, object pdf, string htmlReport, decimal studentId, out string message)
	{
		try
		{
			message = "";

			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_body", OracleType.Blob),
					new OracleParameter("p_id", OracleType.Number)
				};

			spParams[0].Value = studentId;
			spParams[1].Value = pdf;
			spParams[2].Direction = ParameterDirection.Output;


			OraDataAssistant.ExecuteSP(connectionString, "PKG_BB.ADD_WPR_EMAIL", ref spParams, ref message);

			return (decimal)spParams[2].Value;

		}
		catch (Exception ex)
		{
			message = ex.Message;
		}

		return 0;

	}


	public static void SaveSendingDetails(string connectionString, decimal id, string email, string typeName, decimal? status, out string message)
	{
		try
		{
			message = "";

			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_id", OracleType.Number),
					new OracleParameter("p_email", OracleType.VarChar),
					new OracleParameter("p_type", OracleType.VarChar),
					new OracleParameter("p_status", OracleType.Number)
				};

			spParams[0].Value = id;
			spParams[1].Value = email;
			spParams[2].Value = typeName;
			if (status == null)
				spParams[3].Value = DBNull.Value;
			else
				spParams[3].Value = status;

			OraDataAssistant.ExecuteSP(connectionString, "PKG_BB.ADD_WPR_DETAILS", ref spParams, ref message);

		}
		catch (Exception ex)
		{
			message = ex.Message;
		}
	}

	#endregion

	#region Admin

	public void GrantAdmin(string connectionString, string ids, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id_list", OracleType.NVarChar),
				};

			spParams[0].Value = ids;
		

			OraDataAssistant.ExecuteSP(connectionString, "pkg_admin.grant_admin", ref spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}
	}

	public void RevokeAdmin(string connectionString, string ids, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id_list", OracleType.NVarChar),
				};

			spParams[0].Value = ids;


			OraDataAssistant.ExecuteSP(connectionString, "pkg_admin.revoke_admin", ref  spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}
	}

	public void ResetPassword(string connectionString, string ids, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id_list", OracleType.NVarChar),
				};

			spParams[0].Value = ids;


			OraDataAssistant.ExecuteSP(connectionString, "pkg_admin.reset_password", ref  spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}
	}

	public DataTable FindTeachers(string connectionString, char initial, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_initial", OracleType.Char),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			if( initial != ' ')
				spParams[0].Value = initial;
			spParams[1].Direction = ParameterDirection.Output;

			return OraDataAssistant.GetSPDataSet(connectionString, "pkg_admin.find_teachers", spParams, ref message).Tables[0];

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

    [Obsolete("Please use the overload with teather id")]
    public DataTable FindStudents(string connectionString, char initial, out string message)
    {
        message = "";
        try
        {
            message = "";
            OracleParameter[] spParams = new OracleParameter[]
                {	 
                    new OracleParameter("p_initial", OracleType.Char),
                    new OracleParameter("p_result", OracleType.Cursor)
                };

            if (initial != ' ')
                spParams[0].Value = initial;
            spParams[1].Direction = ParameterDirection.Output;

            var st = OraDataAssistant.GetSPDataSet(connectionString, "pkg_admin.find_students", spParams, ref message);

            return st.Tables[0];

        }
        catch (Exception x)
        {
            message = x.Message;
        }

        return null;
    }

    [Obsolete("Please use the overload with teather id")]
    public DataTable FindStudents(string connectionString, string lastname, out string message)
    {
        message = "";
        try
        {
            message = "";
            OracleParameter[] spParams = new OracleParameter[]
                {	 
                    new OracleParameter("p_initial", OracleType.NVarChar),
                    new OracleParameter("p_result", OracleType.Cursor)
                };

            spParams[0].Value = lastname;
            spParams[1].Direction = ParameterDirection.Output;

            return OraDataAssistant.GetSPDataSet(connectionString, "pkg_admin.find_students", spParams, ref message).Tables[0];

        }
        catch (Exception x)
        {
            message = x.Message;
        }

        return null;
    }
	#endregion

	#region Teacher
	public bool IsAuthenticated(string connectionString, string username, string password, out string message)
	{
		Teacher teacher = ReadTeacher(connectionString, username, password, out message);

		return (teacher != null);
	}

	public bool IsMyStudent(string connectionString, decimal teacherId, decimal studentId, decimal semesterId,  out string message)
	{
		try
		{
		
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id", OracleType.Number),
                    new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_semester_id", OracleType.Number),
					new OracleParameter("p_result", OracleType.Number)
				};

			spParams[0].Value = teacherId;
			spParams[1].Value = studentId;
			spParams[2].Value = semesterId;
			spParams[3].Direction = ParameterDirection.Output;

			OraDataAssistant.ExecuteSP(connectionString, "Pkg_teacher.is_my_student", ref spParams, ref message);

			if (spParams[3].Value != null)
				return ((decimal)spParams[3].Value == 1);

			return false;

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return false;
	}


	public Teacher ReadTeacher(string connectionString, string username, string password, out string message)
	{
			DataSet result;
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_user_name", OracleType.VarChar, username.Length + 1),
                    new OracleParameter("p_password", OracleType.VarChar, password.Length + 1),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = username.Trim();
			spParams[1].Value = password.Trim();
			spParams[2].Direction = ParameterDirection.Output;

			result = OraDataAssistant.GetSPDataSet(connectionString, "Pkg_teacher.login", spParams, ref message);
            if (!string.IsNullOrEmpty(message)) throw new Exception(message);

			if (result.Tables[0].Rows.Count == 0)
				return null;

			Teacher teacher = new Teacher();
			if (!result.Tables[0].Rows[0].IsNull("TEACHER_ID"))
				teacher.TEACHER_ID = (decimal)result.Tables[0].Rows[0]["TEACHER_ID"];
			if (!result.Tables[0].Rows[0].IsNull("DCID"))
				teacher.DCID = (decimal)result.Tables[0].Rows[0]["DCID"];
			if (!result.Tables[0].Rows[0].IsNull("USER_NAME"))
				teacher.USER_NAME = (string)result.Tables[0].Rows[0]["USER_NAME"];
			if (!result.Tables[0].Rows[0].IsNull("PASSWORD"))
				teacher.PASSWORD = (string)result.Tables[0].Rows[0]["PASSWORD"];
			if (!result.Tables[0].Rows[0].IsNull("LASTFIRST"))
				teacher.LASTFIRST = (string)result.Tables[0].Rows[0]["LASTFIRST"];
			if (!result.Tables[0].Rows[0].IsNull("FIRST_NAME"))
				teacher.FIRST_NAME = (string)result.Tables[0].Rows[0]["FIRST_NAME"];
			if (!result.Tables[0].Rows[0].IsNull("MIDDLE_NAME"))
				teacher.MIDDLE_NAME = (string)result.Tables[0].Rows[0]["MIDDLE_NAME"];
			if (!result.Tables[0].Rows[0].IsNull("LAST_NAME"))
				teacher.LAST_NAME = (string)result.Tables[0].Rows[0]["LAST_NAME"];
			if (!result.Tables[0].Rows[0].IsNull("STATUS"))
				teacher.STATUS = (decimal)result.Tables[0].Rows[0]["STATUS"];
			if (!result.Tables[0].Rows[0].IsNull("EMAIL_ADDR"))
				teacher.EMAIL_ADDR = (string)result.Tables[0].Rows[0]["EMAIL_ADDR"];
			if (!result.Tables[0].Rows[0].IsNull("IS_ADMIN"))
				teacher.IS_ADMIN = (decimal)result.Tables[0].Rows[0]["IS_ADMIN"];
			if (!result.Tables[0].Rows[0].IsNull("IS_CALL_CENTER"))
				teacher.IS_CALL_CENTER = (decimal)result.Tables[0].Rows[0]["IS_CALL_CENTER"];
			if (!result.Tables[0].Rows[0].IsNull("CHANGE_PWD_FLAG"))
				teacher.CHANGE_PWD_FLAG = (decimal)result.Tables[0].Rows[0]["CHANGE_PWD_FLAG"];
			if (!result.Tables[0].Rows[0].IsNull("SCHOOLID"))
				teacher.SCHOOLID = (decimal)result.Tables[0].Rows[0]["SCHOOLID"];

			return teacher;
	}

	public Teacher ReadTeacher(string connectionString, decimal teacherId, out string message)
	{

		try
		{
			DataSet result;
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = teacherId;
			spParams[1].Direction = ParameterDirection.Output;

			result = OraDataAssistant.GetSPDataSet(connectionString, "pkg_teacher.get_details", spParams, ref message);


			if (result.Tables[0].Rows.Count == 0)
				return null;

			Teacher teacher = new Teacher();
			if (!result.Tables[0].Rows[0].IsNull("TEACHER_ID"))
				teacher.TEACHER_ID = (decimal)result.Tables[0].Rows[0]["TEACHER_ID"];
			if (!result.Tables[0].Rows[0].IsNull("DCID"))
				teacher.DCID = (decimal)result.Tables[0].Rows[0]["DCID"];
			if (!result.Tables[0].Rows[0].IsNull("USER_NAME"))
				teacher.USER_NAME = (string)result.Tables[0].Rows[0]["USER_NAME"];
			if (!result.Tables[0].Rows[0].IsNull("PASSWORD"))
				teacher.PASSWORD = (string)result.Tables[0].Rows[0]["PASSWORD"];
			if (!result.Tables[0].Rows[0].IsNull("LASTFIRST"))
				teacher.LASTFIRST = (string)result.Tables[0].Rows[0]["LASTFIRST"];
			if (!result.Tables[0].Rows[0].IsNull("FIRST_NAME"))
				teacher.FIRST_NAME = (string)result.Tables[0].Rows[0]["FIRST_NAME"];
			if (!result.Tables[0].Rows[0].IsNull("MIDDLE_NAME"))
				teacher.MIDDLE_NAME = (string)result.Tables[0].Rows[0]["MIDDLE_NAME"];
			if (!result.Tables[0].Rows[0].IsNull("LAST_NAME"))
				teacher.LAST_NAME = (string)result.Tables[0].Rows[0]["LAST_NAME"];
			if (!result.Tables[0].Rows[0].IsNull("STATUS"))
				teacher.STATUS = (decimal)result.Tables[0].Rows[0]["STATUS"];
			if (!result.Tables[0].Rows[0].IsNull("EMAIL_ADDR"))
				teacher.EMAIL_ADDR = (string)result.Tables[0].Rows[0]["EMAIL_ADDR"];
			if (!result.Tables[0].Rows[0].IsNull("IS_ADMIN"))
				teacher.IS_ADMIN = (decimal)result.Tables[0].Rows[0]["IS_ADMIN"];
			if (!result.Tables[0].Rows[0].IsNull("IS_CALL_CENTER"))
				teacher.IS_CALL_CENTER =  (decimal)result.Tables[0].Rows[0]["IS_CALL_CENTER"];
			if (!result.Tables[0].Rows[0].IsNull("CHANGE_PWD_FLAG"))
				teacher.CHANGE_PWD_FLAG = (decimal)result.Tables[0].Rows[0]["CHANGE_PWD_FLAG"];
			if (!result.Tables[0].Rows[0].IsNull("SCHOOLID"))
				teacher.SCHOOLID = (decimal)result.Tables[0].Rows[0]["SCHOOLID"];

			return teacher;

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	public void ChangePassword(string connectionString, int teacherId, string newpassword, out string message)
	{

		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id", OracleType.Number),
                    new OracleParameter("p_new_password", OracleType.VarChar, newpassword.Length + 1)
				};

			spParams[0].Value = teacherId;
			spParams[1].Value = newpassword;

			OraDataAssistant.ExecuteSP(connectionString, "Pkg_teacher.password_change", ref spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}
	}


	public DataSet GetSections(string connectionString, decimal teacherId, decimal semester_id, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id", OracleType.Number),
                    new OracleParameter("p_semester_id", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = teacherId;
			spParams[1].Value = semester_id;
			spParams[2].Direction = ParameterDirection.Output;

			return OraDataAssistant.GetSPDataSet(connectionString, "Pkg_teacher.get_sections", spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	public DataSet GetSectionsShort(string connectionString, decimal teacherId, decimal semester_id, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id", OracleType.Number),
                    new OracleParameter("p_semester_id", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = teacherId;
			spParams[1].Value = semester_id;
			spParams[2].Direction = ParameterDirection.Output;


			return OraDataAssistant.GetSPDataSet(connectionString, "Pkg_teacher.get_sections_short", spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}
#endregion
	
	#region Students

	public Student ReadStudent(string connectionString, decimal studentId, out string message)
	{
		try
		{
			DataSet result;
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = studentId;
			spParams[1].Direction = ParameterDirection.Output;

			result = OraDataAssistant.GetSPDataSet(connectionString, "pkg_student.get_details", spParams, ref message);


			if (result.Tables[0].Rows.Count == 0)
				return null;

			Student student = new Student();
			if (!result.Tables[0].Rows[0].IsNull("STUDENT_ID"))
				student.STUDENT_ID = (decimal)result.Tables[0].Rows[0]["STUDENT_ID"];
			if (!result.Tables[0].Rows[0].IsNull("DCID"))
				student.DCID = (decimal)result.Tables[0].Rows[0]["DCID"];
			if (!result.Tables[0].Rows[0].IsNull("LASTFIRST"))
				student.LASTFIRST = (string)result.Tables[0].Rows[0]["LASTFIRST"];
			if (!result.Tables[0].Rows[0].IsNull("FIRST_NAME"))
				student.FIRST_NAME = (string)result.Tables[0].Rows[0]["FIRST_NAME"];
			if (!result.Tables[0].Rows[0].IsNull("MIDDLE_NAME"))
				student.MIDDLE_NAME = (string)result.Tables[0].Rows[0]["MIDDLE_NAME"];
			if (!result.Tables[0].Rows[0].IsNull("LAST_NAME"))
				student.LAST_NAME = (string)result.Tables[0].Rows[0]["LAST_NAME"];

			if (!result.Tables[0].Rows[0].IsNull("STUDENT_NUMBER"))
				student.STUDENT_NUMBER = (decimal)result.Tables[0].Rows[0]["STUDENT_NUMBER"];
			if (!result.Tables[0].Rows[0].IsNull("ENROLL_STATUS"))
				student.ENROLL_STATUS = (decimal)result.Tables[0].Rows[0]["ENROLL_STATUS"];


			if (!result.Tables[0].Rows[0].IsNull("GRADE_LEVEL"))
				student.GRADE_LEVEL = (decimal)result.Tables[0].Rows[0]["GRADE_LEVEL"];
			if (!result.Tables[0].Rows[0].IsNull("SCHOOLID"))
				student.SCHOOLID = (decimal)result.Tables[0].Rows[0]["SCHOOLID"];

			if (!result.Tables[0].Rows[0].IsNull("EMAIL"))
				student.EMAIL = (string)result.Tables[0].Rows[0]["EMAIL"];
			if (!result.Tables[0].Rows[0].IsNull("PHONE"))
				student.PHONE = (string)result.Tables[0].Rows[0]["PHONE"];
			if (!result.Tables[0].Rows[0].IsNull("HOME_PHONE"))
				student.HOME_PHONE = (string)result.Tables[0].Rows[0]["HOME_PHONE"];
			if (!result.Tables[0].Rows[0].IsNull("STREET"))
				student.STREET = (string)result.Tables[0].Rows[0]["STREET"];
			if (!result.Tables[0].Rows[0].IsNull("STATE"))
				student.STATE = (string)result.Tables[0].Rows[0]["STATE"];
			if (!result.Tables[0].Rows[0].IsNull("CITY"))
				student.CITY = (string)result.Tables[0].Rows[0]["CITY"];
			if (!result.Tables[0].Rows[0].IsNull("ZIP"))
				student.ZIP = (string)result.Tables[0].Rows[0]["ZIP"];

			if (!result.Tables[0].Rows[0].IsNull("GENDER"))
				student.GENDER = (string)result.Tables[0].Rows[0]["GENDER"];


			if (!result.Tables[0].Rows[0].IsNull("PARENT_NAME"))
				student.PARENT_NAME = (string)result.Tables[0].Rows[0]["PARENT_NAME"];


			if (!result.Tables[0].Rows[0].IsNull("PARENT_PHONE"))
				student.PARENT_PHONE = (string)result.Tables[0].Rows[0]["PARENT_PHONE"];


			if (!result.Tables[0].Rows[0].IsNull("PARENT_EMAIL"))
				student.PARENT_EMAIL = (string)result.Tables[0].Rows[0]["PARENT_EMAIL"];

			return student;

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

    /// <summary>
    /// Function which returns Blast Off Report DataTable
    /// </summary>
    /// <param name="connectionString">Database connectionString</param>
    /// <param name="teacherID">Teacher identifier</param>
    /// <param name="studentName">Student name</param>
    /// <param name="sectionId">Course identifier</param>
    /// <param name="message">Error message</param>
    /// <returns>Blast Off Report DataTable</returns>
    public DataTable GetBlastOffReportData(string connectionString, decimal teacherID, string studentName, decimal? sectionId, out string message)
    {
        message = "";
        DataTable dt = new DataTable();
        try
        {
            message = "";
            OracleParameter[] spParams = new OracleParameter[]
                {	 
                    new OracleParameter("p_teacher_id", OracleType.Number),
                    new OracleParameter("p_student_name", OracleType.VarChar), 
                    new OracleParameter("p_section_id", OracleType.Number), 
                    new OracleParameter("p_result", OracleType.Cursor)
                };

            spParams[0].Value = teacherID;
            spParams[1].Value = studentName;
            spParams[2].Value = sectionId;
            spParams[3].Direction = ParameterDirection.Output;

            var dataSet = OraDataAssistant.GetSPDataSet(connectionString, "pkg_bb.blast_off_report", spParams, ref message);

            if (dataSet == null)
                throw new InvalidOperationException("Error calling stored procedure 'pkg_bb.blast_off_report'. " + message);
            else
                return dataSet.Tables[0];
        }
        catch (Exception ex)
        {
            message = ex.Message;
        }

        return null;
    }

    public DataTable GetRainbowReportData(string connectionString, decimal teacherID, string studentName, decimal? sectionId, out string message)
    {
        message = "";
        DataTable dt = new DataTable();
        //try
        {
            message = "";
            OracleParameter[] spParams = new OracleParameter[]
                {	 
                    new OracleParameter("p_teacher_id", OracleType.Number),
                    new OracleParameter("p_student_name", OracleType.VarChar), 
                    new OracleParameter("p_section_id", OracleType.Number), 
                    new OracleParameter("p_result", OracleType.Cursor)
                };

            spParams[0].Value = teacherID;
            spParams[1].Value = studentName;
            spParams[2].Value = sectionId;
            spParams[3].Direction = ParameterDirection.Output;

            var dataSet = OraDataAssistant.GetSPDataSet(connectionString, "pkg_bb.rainbow_report", spParams, ref message);

            if (dataSet == null)
                throw new InvalidOperationException("Error calling stored procedure 'pkg_student.find_students'. " + message);
            else
                return dataSet.Tables[0];
        }
        //catch (Exception ex)
        //{
        //    message = ex.Message;
        //}

        return null;
    }

    /// <summary>
    /// Function which returns Teacher Tracker Report DataTable
    /// </summary>
    /// <param name="connectionString">Database connectionString</param>
    /// <param name="teacherID">Teacher identifier</param>
    /// <param name="teacherName">Teacher name</param>
    /// <param name="sectionId">Course identifier</param>
    /// <param name="message">Error message</param>
    /// <returns>Teacher Tracker Report DataTable</returns>
    public DataTable GetTeacherTrackerReportData(string connectionString, decimal teacherID, string teacherName, decimal? sectionId, out string message)
    {
        message = "";
        DataTable dt = new DataTable();
        try
        {
            message = "";
            OracleParameter[] spParams = new OracleParameter[]
                {	 
                    new OracleParameter("p_teacher_id", OracleType.Number),
                    new OracleParameter("p_teacher_name", OracleType.VarChar), 
                    new OracleParameter("p_section_id", OracleType.Number), 
                    new OracleParameter("p_result", OracleType.Cursor)
                };

            spParams[0].Value = teacherID;
            spParams[1].Value = teacherName;
            spParams[2].Value = sectionId;
            spParams[3].Direction = ParameterDirection.Output;

            var dataSet = OraDataAssistant.GetSPDataSet(connectionString, "pkg_bb.teacher_tracking_report", spParams, ref message);

            if (dataSet == null)
                throw new InvalidOperationException("Error calling stored procedure 'pkg_bb.teacher_tracking_report'. " + message);
            else
                return dataSet.Tables[0];
        }
        catch (Exception ex)
        {
            message = ex.Message;
        }

        return null;
    }
    
	public Current GetCurrent(string connectionString, out string message)
	{
		Current result = new Current();

		try
		{
			DataSet ds;
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Direction = ParameterDirection.Output;

			ds = OraDataAssistant.GetSPDataSet(connectionString, "pkg_semester.get_current", spParams, ref message);

			result.SemesterId = (decimal)ds.Tables[0].Rows[0]["SEMESTER_ID"];
			result.TermId = (decimal)ds.Tables[0].Rows[0]["TERM_ID"];

			result.TERM_NAME = (string)ds.Tables[0].Rows[0]["TERM_NAME"];
			result.LAST_SAT = (DateTime)ds.Tables[0].Rows[0]["LAST_SAT"];
			result.FIRST_SUN = (DateTime)ds.Tables[0].Rows[0]["FIRST_SUN"];
			result.FIRSTDAY = (DateTime)ds.Tables[0].Rows[0]["FIRSTDAY"];
			result.LASTDAY = (DateTime)ds.Tables[0].Rows[0]["LASTDAY"];
			result.SNO = (decimal)ds.Tables[0].Rows[0]["SNO"];
			result.NAME = (string)ds.Tables[0].Rows[0]["NAME"];

			return result;

		}
		catch (Exception x)
		{
			message = x.Message;
            throw;
		}

		return null;
	}

    public DataTable FindStudents(string connectionString, string initial, decimal teacherID, bool enrolled, out string message)
    {
        message = "";
        try
        {
            message = "";
            OracleParameter[] spParams = new OracleParameter[]
                {	 
                    new OracleParameter("p_teacher_id", OracleType.Number),
                    new OracleParameter("p_initial", OracleType.NVarChar),
                    new OracleParameter("p_enrolled", OracleType.Number),
                    new OracleParameter("p_result", OracleType.Cursor)
                };

            spParams[0].Value = teacherID;
            spParams[1].Value = initial.Trim();
            spParams[2].Value = enrolled ? 1 : 0;
            spParams[3].Direction = ParameterDirection.Output;

            var dataSet = OraDataAssistant.GetSPDataSet(connectionString, "pkg_student.find_students", spParams, ref message);

            if (dataSet == null)
                throw new InvalidOperationException("Error calling stored procedure 'pkg_student.find_students'. " + message);
            else
                return dataSet.Tables[0];

        }
        catch (Exception x)
        {
            message = x.Message;
            throw;
        }

        return null;
    }

    public DataTable FindStudentsAdmin(string connectionString, string initial, bool enrolled, out string message)
    {
        message = "";
        try
        {
            message = "";
            OracleParameter[] spParams = new OracleParameter[]
                {	 
                    new OracleParameter("p_initial", OracleType.NVarChar),
                    new OracleParameter("p_enrolled", OracleType.Number),
                    new OracleParameter("p_result", OracleType.Cursor)
                };

            spParams[0].Value = initial.Trim();
            spParams[1].Value = enrolled ? 1 : 0;
            spParams[2].Direction = ParameterDirection.Output;

            var dataSet = OraDataAssistant.GetSPDataSet(connectionString, "pkg_admin.find_students", spParams, ref message);

            if (dataSet == null)
                throw new InvalidOperationException("Error calling stored procedure 'pkg_admin.find_students'. " + message);
            else
                return dataSet.Tables[0];

        }
        catch (Exception x)
        {
            message = x.Message;
            throw;
        }

        return null;
    }

    public DataTable GetStudentGrades(string connectionString, decimal teacherID, decimal studentID, out string message)
    {
        message = "";
        try
        {
            message = "";
            OracleParameter[] spParams = new OracleParameter[]
                {	
                    new OracleParameter("p_result", OracleType.Cursor) ,
                    new OracleParameter("p_teacher_id", OracleType.Number),
                    new OracleParameter("p_student_id", OracleType.Number)
                };

            spParams[0].Direction = ParameterDirection.Output;
            spParams[1].Value = teacherID;
            spParams[2].Value = studentID;

            var dataSet = OraDataAssistant.GetSPDataSet(connectionString, "PKG_BB.get_student_grades", spParams, ref message);

            if (dataSet == null)
                throw new InvalidOperationException("Error calling stored procedure 'PKG_BB.GET_STUDENT_GRADES'. " + message);
            else
                return dataSet.Tables[0];

        }
        catch (Exception x)
        {
            message = x.Message;
        }

        return null;
    }

    public DataTable GetSectionGrades(string connectionString, decimal teacherID, decimal studentID, decimal sectionID, out string message)
    {
      string gradeToDate;
      var dataSet = GetSectionGrades(connectionString, teacherID, studentID, sectionID, out gradeToDate, out message);

      if (dataSet != null)
      {
        DataRow dr = dataSet.NewRow();
        dr["item_name"] = "Grade To Date";
        dr["GRADE"] = gradeToDate;

        dataSet.Rows.InsertAt(dr, 0);
        return dataSet;
      }

      return null;
    }

    public DataTable GetSectionGrades(string connectionString, decimal teacherID, decimal studentID, decimal sectionID, out string gradeToDate, out string message)
    {
      message = "";
      try
      {
        message = "";
        OracleParameter[] spParams = new OracleParameter[]
                {	
                    new OracleParameter("p_result", OracleType.Cursor) ,
                    new OracleParameter("p_teacher_id", OracleType.Number),
                    new OracleParameter("p_student_id", OracleType.Number),
                    new OracleParameter("p_section_id", OracleType.Number),
                    new OracleParameter("p_grade_to_date", OracleType.VarChar)
                };

        spParams[0].Direction = ParameterDirection.Output;
        spParams[4].Direction = ParameterDirection.Output;
        spParams[4].Size = 4000;

        spParams[1].Value = teacherID;
        spParams[2].Value = studentID;
        spParams[3].Value = sectionID;

        var dataSet = OraDataAssistant.GetSPDataSet(connectionString, "PKG_BB.get_section_grades", spParams, ref message);

        if (dataSet == null)
          throw new InvalidOperationException("Error calling stored procedure 'PKG_BB.GET_STUDENT_GRADES'. " + message);
        else
        {
          gradeToDate = spParams[4].Value.ToString();
          return dataSet.Tables[0];
        }

      }
      catch (Exception x)
      {
        message = x.Message;
      }

      gradeToDate = "";
      return null;
    }

    /// <summary>
    /// Function which returns Sections DataTable
    /// </summary>
    /// <param name="connectionString">DataBase connectionString</param>
    /// <param name="message">error message</param>
    /// <returns>Sections DataTable</returns>
    public DataTable GetSections(string connectionString, out string message)
    {
        message = "";
        try
        {
            message = "";
            OracleParameter[] spParams = new OracleParameter[]
                {	
                    new OracleParameter("p_result", OracleType.Cursor) //,
                    //new OracleParameter("p_teacher_id", OracleType.Number),
                    //new OracleParameter("p_student_id", OracleType.Number)
                };

            spParams[0].Direction = ParameterDirection.Output;
            //spParams[1].Value = teacherID;
            //spParams[2].Value = studentID;

            var dataSet = OraDataAssistant.GetSPDataSet(connectionString, "PKG_BB.GET_All_COURSES", spParams, ref message);

            if (dataSet == null)
                throw new InvalidOperationException("Error calling stored procedure 'PKG_BB.GET_STUDENT_GRADES'. " + message);
            else
                return dataSet.Tables[0];
        }
        catch (Exception x)
        {
            message = x.Message;
        }

        return null;
    }

#endregion

	#region Addendance

	public DataTable GetStudentAlRecords(string connectionString, decimal studentId, 
		DateTime from, 
		DateTime to,
		out string message)
	{
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_from_date", OracleType.DateTime),
					new OracleParameter("p_to_date", OracleType.DateTime),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = studentId;
			spParams[1].Value = from;
			spParams[2].Value = to;
			spParams[3].Direction = ParameterDirection.Output;

			return OraDataAssistant.GetSPDataSet(connectionString, "pkg_attendance.get_student_att_records", spParams, ref message).Tables[0];

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	public DataTable GetEnrolledStudents(string connectionString, decimal sectionId, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_section_id", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = sectionId;
			spParams[1].Direction = ParameterDirection.Output;

			return OraDataAssistant.GetSPDataSet(connectionString, "pkg_attendance.get_enrolled_students", spParams, ref message).Tables[0];

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	public DataTable GetRecords(string connectionString, decimal sectionId, DateTime start, DateTime end, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_section_id", OracleType.Number),
					new OracleParameter("p_from_date", OracleType.DateTime),
					new OracleParameter("p_to_date", OracleType.DateTime),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = sectionId;
			spParams[1].Value = start;
			spParams[2].Value = end;
			spParams[3].Direction = ParameterDirection.Output;

			return OraDataAssistant.GetSPDataSet(connectionString, "pkg_attendance.get_attendance_records", spParams, ref message).Tables[0];

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	public DataTable GetCodes(string connectionString, decimal termId, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_term_id", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = termId;
			spParams[1].Direction = ParameterDirection.Output;

			return OraDataAssistant.GetSPDataSet(connectionString, "pkg_attendance.get_attendance_codes", spParams, ref message).Tables[0];

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}


	public void SetAttendance(string connectionString, decimal p_enrollment_id, DateTime p_att_date, decimal p_att_code_id, string p_comment, out string message)
	{
		message = "";
		
		OracleParameter[] spParams = new OracleParameter[]
				{	 

					new OracleParameter("p_enrollment_id", OracleType.Number),
                    new OracleParameter("p_att_date", OracleType.DateTime),
					new OracleParameter("p_att_code_id", OracleType.Number),
					new OracleParameter("p_comment", OracleType.VarChar, p_comment.Length + 1)
				};

		spParams[0].Value = p_enrollment_id;
		spParams[1].Value = p_att_date;
		spParams[2].Value = p_att_code_id;
		spParams[3].Value = p_comment.Trim();
		
		
		OraDataAssistant.ExecuteSP(connectionString, "pkg_attendance.set_attendance", ref spParams, ref message);
	}

	public void SetAttendance(
		OracleConnection connection, 
		OracleTransaction transaction, 
		decimal p_enrollment_id, 
		DateTime p_att_date, 
		decimal p_att_code_id, 
		string p_comment, 
		string p_other_codes,
		out string message)
	{
		message = "";
		
		OracleParameter[] spParams = new OracleParameter[]
				{	 

					new OracleParameter("p_enrollment_id", OracleType.Number),
                    new OracleParameter("p_att_date", OracleType.DateTime),
					new OracleParameter("p_att_code_id", OracleType.Number),
					new OracleParameter("p_comment", OracleType.VarChar, p_comment.Length + 1),
					new OracleParameter("p_other_codes", OracleType.VarChar, p_other_codes.Length + 1),
				};

		spParams[0].Value = p_enrollment_id;
		spParams[1].Value = p_att_date;
		spParams[2].Value = p_att_code_id;
		spParams[3].Value = p_comment.Trim();
		spParams[4].Value = p_other_codes.Trim();
	
		OracleCommand command = new OracleCommand("pkg_attendance.set_attendance", connection, transaction);
		command.CommandType = CommandType.StoredProcedure;
		foreach (OracleParameter parameter in spParams)
		{
			command.Parameters.Add(parameter);
		}

		command.ExecuteNonQuery();
	}
	 
	#region Log Entries
	public DataSet GetLogTypes(string connectionString, decimal p_include_special, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_include_special", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = p_include_special;
			spParams[1].Direction = ParameterDirection.Output;

			return OraDataAssistant.GetSPDataSet(connectionString, "pkg_log.get_log_types", spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	public DataSet GetLogEntries(string connectionString, decimal student_id, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = student_id;
			spParams[1].Direction = ParameterDirection.Output;

			return OraDataAssistant.GetSPDataSet(connectionString, "pkg_student	.get_log_entries", spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}


	public DataSet GetLogEntries(string connectionString, decimal student_id, decimal logtype, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_log_type", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = student_id;
			spParams[1].Value = logtype;
			spParams[2].Direction = ParameterDirection.Output;

			return OraDataAssistant.GetSPDataSet(connectionString, "pkg_log.get_log_entries", spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	


	public void UpdateLogEntries(string connectionString,
		decimal log_id,
		decimal teacher_id,
		string entry,
		decimal logtype_id,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_log_id", OracleType.Number),
					new OracleParameter("p_teacher_id", OracleType.Number),
					new OracleParameter("p_entry",  OracleType.Clob, entry.Length + 1),
					new OracleParameter("p_log_type_id", OracleType.Number)
				};

			spParams[0].Value = log_id;
			spParams[1].Value = teacher_id;
			spParams[2].Value = entry.Trim();
			spParams[3].Value = logtype_id;


			OraDataAssistant.ExecuteSP(connectionString, "pkg_log.update_log_entry", ref spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}

	}


	public void AddLogEntriesByTeacher(string connectionString,
		decimal p_teacher_id,
		decimal student_id,
		string entry,
		decimal logtype_id,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_teacher_id",  OracleType.Number),
					new OracleParameter("p_entry",OracleType.Clob, entry.Length + 1),
					new OracleParameter("p_log_type_id", OracleType.Number),
					new OracleParameter("p_log_id", OracleType.Number)
				};

			spParams[0].Value = student_id;
			spParams[1].Value = p_teacher_id;
			spParams[2].Value = entry.Trim();
			spParams[3].Value = logtype_id;
			spParams[4].Direction = ParameterDirection.Output;

			OraDataAssistant.ExecuteSP(connectionString, "pkg_log.add_log_entry", ref spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}
	}


	public void AddLogEntries(string connectionString,
		decimal p_section_id,
		decimal student_id,
		string entry,
		decimal logtype_id,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_section_id", OracleType.Number),
					new OracleParameter("p_student_id",  OracleType.Number),
					new OracleParameter("p_entry",OracleType.Clob, entry.Length + 1),
					new OracleParameter("p_log_type_id", OracleType.Number)
				};

			spParams[0].Value = p_section_id;
			spParams[1].Value = student_id;
			spParams[2].Value = entry.Trim();
			spParams[3].Value = logtype_id;

			OraDataAssistant.ExecuteSP(connectionString, "pkg_log.add_log_entry", ref spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}
	}


	public void AddLogEntriesForProfile(string connectionString,
		decimal p_student_id,
		decimal p_teacher_id,
		string entry,
		decimal logtype_id,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_teacher_id",  OracleType.Number),
					new OracleParameter("p_entry",OracleType.Clob, entry.Length + 1),
					new OracleParameter("p_log_type_id", OracleType.Number),
					new OracleParameter("p_log_id", OracleType.Number)
				};

			spParams[0].Value = p_student_id;
			spParams[1].Value = p_teacher_id;
			spParams[2].Value = entry.Trim();
			spParams[3].Value = logtype_id;
			spParams[4].Direction = ParameterDirection.Output; ;

			OraDataAssistant.ExecuteSP(connectionString, "pkg_log.add_log_entry", ref spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}
	}
	#endregion
	#region Teachers
	public DataSet GetTeachers(string connectionString, decimal semesterId, out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_semester_id", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = semesterId;
			spParams[1].Direction = ParameterDirection.Output;

			DataSet result = OraDataAssistant.GetSPDataSet(connectionString, "pkg_semester.get_teachers", spParams, ref message);
			DataTable dt = result.Tables[0];
			DataRow dr = dt.NewRow();
			dr["TEACHER_ID"] = 0;
			dr["TEACHER_NAME"] = " All Teachers";
			dt.Rows.InsertAt(dr, 0);
			return result;
		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	public DataSet GetAlerts(string connectionString,
		decimal section_id,
		DateTime weekDate,
		decimal teacherId,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_section_id", OracleType.Number),
					new OracleParameter("p_week_date", OracleType.DateTime),
					new OracleParameter("p_teacher_id", OracleType.Number),
					new OracleParameter("p_result", OracleType.Cursor)
				};

			spParams[0].Value = section_id;
			spParams[1].Value = weekDate;
			spParams[2].Value = teacherId;
			spParams[3].Direction = ParameterDirection.Output;

			return OraDataAssistant.GetSPDataSet(connectionString, "pkg_attendance.get_alerts", spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}

		return null;
	}

	#endregion

	public void Excuse_Absence(string connectionString,
		decimal teacherId,
		decimal studentId,
		DateTime startDate,
		string memo,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id", OracleType.Number),
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_start_date", OracleType.DateTime),
					new OracleParameter("p_memo", OracleType.VarChar, memo.Length + 1)
				};
			spParams[0].Value = teacherId;
			spParams[1].Value = studentId;
			spParams[2].Value = startDate;
			spParams[3].Value = memo.Trim();

			OraDataAssistant.ExecuteSP(connectionString, "pkg_student.excuse_absence", ref spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}
	}

	public void Save_Call(string connectionString,
		decimal teacherId,
		decimal studentId,
		string memo,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id", OracleType.Number),
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_message", OracleType.Clob, memo.Length + 1)
				};

			spParams[0].Value = teacherId;
			spParams[1].Value = studentId;
			spParams[2].Value = memo.Trim();

			OraDataAssistant.ExecuteSP(connectionString, "pkg_student.save_call", ref spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}
	}

	public void Save_Email(string connectionString,
		decimal teacherId,
		decimal studentId,
		string emailaddress,
		string subject,
		string body,
		out string message)
	{
		message = "";
		try
		{
			message = "";
			OracleParameter[] spParams = new OracleParameter[]
				{	 
					new OracleParameter("p_teacher_id", OracleType.Number),
					new OracleParameter("p_student_id", OracleType.Number),
					new OracleParameter("p_email_address", OracleType.VarChar, emailaddress.Length + 1),
					new OracleParameter("p_email_subject", OracleType.VarChar, subject.Length + 1),
					new OracleParameter("p_email_body", OracleType.Clob, body.Length + 1)
				};

			spParams[0].Value = teacherId;
			spParams[1].Value = studentId;
			spParams[2].Value = emailaddress.Trim();
			spParams[3].Value = subject.Trim();
			spParams[4].Value = body.Trim(); ;
			

			OraDataAssistant.ExecuteSP(connectionString, "pkg_student.save_email", ref spParams, ref message);

		}
		catch (Exception x)
		{
			message = x.Message;
		}
	}
	#endregion

	#region Log

	#endregion
}